/************************************************************************************/
/*  Program name CAPAPIMP_CHECKS.sql                                                */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

ALTER TRIGGER AP_CHECKS_CBEI DISABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU1 DISABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU2 DISABLE;
ALTER TRIGGER AX_AP_CHECKS_BRI1 DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPAPIMP_CHECKS';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_AP_CHECKS;

	lv_Next_Seq_ID	NUMBER;
	lv_Vend_ID		NUMBER;

BEGIN

	FOR C1 IN Cur_F LOOP
		BEGIN
			SELECT	AP_CHECKS_S.NextVal
			INTO	lv_Next_Seq_ID
			FROM	DUAL;

			INSERT INTO CRST_AP_CHECKS_MAP VALUES
			(
			C1.CHECK_ID,
			C1.CHECK_STOCK_ID,
			C1.CHECK_NUMBER,
			C1.DOC_SEQUENCE_ID,
			C1.DOC_SEQUENCE_VALUE,
			lv_Next_Seq_ID,
			CAPBRIMP_MAPPING.GetCheckStockIDMapping(C1.CHECK_STOCK_ID),
			C1.CHECK_NUMBER,
			C1.DOC_SEQUENCE_ID,		--NULL
			C1.DOC_SEQUENCE_VALUE	--NULL
			);

			lv_Vend_ID	:=	CAPBRIMP_COMMON.GetVendorID_Name(C1.VENDOR_NAME);

			INSERT INTO AP_CHECKS_ALL
			(
			AMOUNT             			   ,
			BANK_ACCOUNT_ID                ,  
			BANK_ACCOUNT_NAME              ,
			CHECK_DATE                     , 
			CHECK_ID                       ,  
			CHECK_NUMBER				   ,                   	     
			CURRENCY_CODE                  ,  
			LAST_UPDATED_BY                ,  
			LAST_UPDATE_DATE               , 
			PAYMENT_METHOD_LOOKUP_CODE     ,
			PAYMENT_TYPE_FLAG              ,
			ADDRESS_LINE1                  ,
			ADDRESS_LINE2                  ,
			ADDRESS_LINE3                  ,
			CHECKRUN_NAME                  ,
			CHECK_FORMAT_ID                ,  
			CHECK_STOCK_ID                 ,  
			CITY                           ,
			COUNTRY                        ,
			CREATED_BY                     ,  
			CREATION_DATE                  , 
			LAST_UPDATE_LOGIN              ,  
			STATUS_LOOKUP_CODE             ,
			VENDOR_NAME                    ,
			VENDOR_SITE_CODE               ,  
			ZIP                            ,
			BANK_ACCOUNT_NUM               ,  
			BANK_ACCOUNT_TYPE              ,
			BANK_NUM                       ,
			CHECK_VOUCHER_NUM              ,
			CLEARED_AMOUNT                 ,
			CLEARED_DATE                   , 
			DOC_CATEGORY_CODE              ,  
			DOC_SEQUENCE_ID                ,
			DOC_SEQUENCE_VALUE             ,
			PROVINCE                       ,  
			RELEASED_AT                    ,
			RELEASED_BY                    ,  
			STATE                          ,  
			STOPPED_AT                     ,
			STOPPED_BY                     ,  
			VOID_DATE                      , 
			ATTRIBUTE1                     ,  
			ATTRIBUTE10                    ,  
			ATTRIBUTE11                    ,  
			ATTRIBUTE12                    ,  
			ATTRIBUTE13                    ,  
			ATTRIBUTE14                    ,  
			ATTRIBUTE15                    ,  
			ATTRIBUTE2                     ,  
			ATTRIBUTE3                     ,  
			ATTRIBUTE4                     ,  
			ATTRIBUTE5                     ,  
			ATTRIBUTE6                     ,  
			ATTRIBUTE7                     ,  
			ATTRIBUTE8                     ,  
			ATTRIBUTE9                     ,  
			ATTRIBUTE_CATEGORY             ,  
			FUTURE_PAY_DUE_DATE            , 
			TREASURY_PAY_DATE              , 
			TREASURY_PAY_NUMBER            ,  
			USSGL_TRANSACTION_CODE         ,  
			USSGL_TRX_CODE_CONTEXT         ,  
			WITHHOLDING_STATUS_LOOKUP_CODE ,
			RECONCILIATION_BATCH_ID        ,  
			CLEARED_BASE_AMOUNT            ,
			CLEARED_EXCHANGE_RATE          ,
			CLEARED_EXCHANGE_DATE          , 
			CLEARED_EXCHANGE_RATE_TYPE     ,  
			ADDRESS_LINE4                  ,
			COUNTY                         ,  
			ADDRESS_STYLE                  ,  
			ORG_ID                         ,  
			VENDOR_ID                      ,  
			VENDOR_SITE_ID                 ,  
			EXCHANGE_RATE                  ,
			EXCHANGE_DATE                  , 
			EXCHANGE_RATE_TYPE             ,  
			BASE_AMOUNT                    ,
			CHECKRUN_ID                    ,  
			REQUEST_ID                     ,  
			CLEARED_ERROR_AMOUNT           ,
			CLEARED_CHARGES_AMOUNT         ,
			CLEARED_ERROR_BASE_AMOUNT      ,
			CLEARED_CHARGES_BASE_AMOUNT    ,
			POSITIVE_PAY_STATUS_CODE       ,
			GLOBAL_ATTRIBUTE_CATEGORY      ,  
			GLOBAL_ATTRIBUTE1              ,  
			GLOBAL_ATTRIBUTE2              ,  
			GLOBAL_ATTRIBUTE3              ,  
			GLOBAL_ATTRIBUTE4              ,  
			GLOBAL_ATTRIBUTE5              ,  
			GLOBAL_ATTRIBUTE6              ,  
			GLOBAL_ATTRIBUTE7              ,  
			GLOBAL_ATTRIBUTE8              ,  
			GLOBAL_ATTRIBUTE9              ,  
			GLOBAL_ATTRIBUTE10             ,  
			GLOBAL_ATTRIBUTE11             ,  
			GLOBAL_ATTRIBUTE12             ,  
			GLOBAL_ATTRIBUTE13             ,  
			GLOBAL_ATTRIBUTE14             ,  
			GLOBAL_ATTRIBUTE15             ,  
			GLOBAL_ATTRIBUTE16             ,  
			GLOBAL_ATTRIBUTE17             ,  
			GLOBAL_ATTRIBUTE18             ,  
			GLOBAL_ATTRIBUTE19             ,  
			GLOBAL_ATTRIBUTE20             ,  
			TRANSFER_PRIORITY              , 
			EXTERNAL_BANK_ACCOUNT_ID       ,  
			STAMP_DUTY_AMT                 ,
			STAMP_DUTY_BASE_AMT            ,
			MRC_CLEARED_BASE_AMOUNT        ,  
			MRC_CLEARED_EXCHANGE_RATE      ,  
			MRC_CLEARED_EXCHANGE_DATE      ,  
			MRC_CLEARED_EXCHANGE_RATE_TYPE ,  
			MRC_EXCHANGE_RATE              ,  
			MRC_EXCHANGE_DATE              ,  
			MRC_EXCHANGE_RATE_TYPE         ,  
			MRC_BASE_AMOUNT                ,  
			MRC_CLEARED_ERROR_BASE_AMOUNT  ,  
			MRC_CLEARED_CHARGES_BASE_AMT   ,  
			MRC_STAMP_DUTY_BASE_AMT        ,  
			MATURITY_EXCHANGE_DATE         , 
			MATURITY_EXCHANGE_RATE_TYPE    ,  
			MATURITY_EXCHANGE_RATE         ,
			DESCRIPTION                    , 
			ACTUAL_VALUE_DATE              , 
			ANTICIPATED_VALUE_DATE         , 
			RELEASED_DATE                  , 
			STOPPED_DATE                   , 
			MRC_MATURITY_EXG_DATE          ,  
			MRC_MATURITY_EXG_RATE          ,  
			MRC_MATURITY_EXG_RATE_TYPE     ,  
			IBAN_NUMBER                      
			)
			VALUES
			(
			C1.AMOUNT,
			CAPBRIMP_MAPPING.GetBankACMapping(C1.BANK_ACCOUNT_ID),		--BANK_ACCOUNT_ID,
			C1.BANK_ACCOUNT_NAME,
			C1.CHECK_DATE,
			lv_Next_Seq_ID,												--CHECK_ID,
			C1.CHECK_NUMBER,
			C1.CURRENCY_CODE,
			CAPBRIMP_COMMON.GetUser(C1.LAST_UPDATED_BY_NAME),			--LAST_UPDATED_BY,
			C1.LAST_UPDATE_DATE,
			C1.PAYMENT_METHOD_LOOKUP_CODE,
			C1.PAYMENT_TYPE_FLAG,
			C1.ADDRESS_LINE1,
			C1.ADDRESS_LINE2,
			C1.ADDRESS_LINE3,
			C1.CHECKRUN_NAME,
			CAPBRIMP_MAPPING.GetPayFormatMapping(C1.CHECK_FORMAT_ID),	--CHECK_FORMAT_ID
			CAPBRIMP_MAPPING.GetCheckStockIDMapping(C1.CHECK_STOCK_ID),	--CHECK_STOCK_ID
			C1.CITY,
			C1.COUNTRY,
			CAPBRIMP_COMMON.GetUser(C1.CREATED_BY_NAME),				--CREATED_BY,
			C1.CREATION_DATE,
			C1.LAST_UPDATE_LOGIN,
			C1.STATUS_LOOKUP_CODE,
			C1.VENDOR_NAME,
			C1.VENDOR_SITE_CODE,
			C1.ZIP,
			C1.BANK_ACCOUNT_NUM,
			C1.BANK_ACCOUNT_TYPE,
			C1.BANK_NUM,
			C1.CHECK_VOUCHER_NUM,
			C1.CLEARED_AMOUNT,
			C1.CLEARED_DATE,
			C1.DOC_CATEGORY_CODE,
			C1.DOC_SEQUENCE_ID,
			C1.DOC_SEQUENCE_VALUE,
			C1.PROVINCE,
			C1.RELEASED_AT,
			CAPBRIMP_COMMON.GetUser(C1.RELEASED_BY_NAME),				--RELEASED_BY,
			C1.STATE,
			C1.STOPPED_AT,
			CAPBRIMP_COMMON.GetUser(C1.STOPPED_BY_NAME),				--STOPPED_BY,
			C1.VOID_DATE,
			C1.ATTRIBUTE1,
			C1.ATTRIBUTE10,
			C1.ATTRIBUTE11,
			C1.ATTRIBUTE12,
			C1.ATTRIBUTE13,
			C1.ATTRIBUTE14,
			C1.ATTRIBUTE15,
			C1.ATTRIBUTE2,
			C1.ATTRIBUTE3,
			C1.ATTRIBUTE4,
			C1.ATTRIBUTE5,
			C1.ATTRIBUTE6,
			C1.ATTRIBUTE7,
			C1.ATTRIBUTE8,
			C1.ATTRIBUTE9,
			C1.ATTRIBUTE_CATEGORY,
			C1.FUTURE_PAY_DUE_DATE,
			C1.TREASURY_PAY_DATE,
			C1.TREASURY_PAY_NUMBER,
			C1.USSGL_TRANSACTION_CODE,
			C1.USSGL_TRX_CODE_CONTEXT,
			C1.WITHHOLDING_STATUS_LOOKUP_CODE,
			C1.RECONCILIATION_BATCH_ID,
			C1.CLEARED_BASE_AMOUNT,
			C1.CLEARED_EXCHANGE_RATE,
			C1.CLEARED_EXCHANGE_DATE,
			C1.CLEARED_EXCHANGE_RATE_TYPE,
			C1.ADDRESS_LINE4,
			C1.COUNTY,
			C1.ADDRESS_STYLE,
			CAPBRIMP_COMMON.GetOrgID(C1.ORG_NAME),					--ORG_ID,
			lv_Vend_ID,												--VENDOR_ID,
			CAPBRIMP_COMMON.GetVendorSiteID(lv_Vend_ID, CAPBRIMP_COMMON.GetOrgID(C1.ORG_NAME), C1.VENDOR_SITE_CODE),	--VENDOR_SITE_ID,
			C1.EXCHANGE_RATE,
			C1.EXCHANGE_DATE,
			C1.EXCHANGE_RATE_TYPE,
			C1.BASE_AMOUNT,
			CAPBRIMP_MAPPING.GetAPInvSelCrtIDMapping(C1.CHECKRUN_ID),	--CHECKRUN_ID,
			C1.REQUEST_ID,
			C1.CLEARED_ERROR_AMOUNT,
			C1.CLEARED_CHARGES_AMOUNT,
			C1.CLEARED_ERROR_BASE_AMOUNT,
			C1.CLEARED_CHARGES_BASE_AMOUNT,
			C1.POSITIVE_PAY_STATUS_CODE,
			C1.GLOBAL_ATTRIBUTE_CATEGORY,
			C1.GLOBAL_ATTRIBUTE1,
			C1.GLOBAL_ATTRIBUTE2,
			C1.GLOBAL_ATTRIBUTE3,
			C1.GLOBAL_ATTRIBUTE4,
			C1.GLOBAL_ATTRIBUTE5,
			C1.GLOBAL_ATTRIBUTE6,
			C1.GLOBAL_ATTRIBUTE7,
			C1.GLOBAL_ATTRIBUTE8,
			C1.GLOBAL_ATTRIBUTE9,
			C1.GLOBAL_ATTRIBUTE10,
			C1.GLOBAL_ATTRIBUTE11,
			C1.GLOBAL_ATTRIBUTE12,
			C1.GLOBAL_ATTRIBUTE13,
			C1.GLOBAL_ATTRIBUTE14,
			C1.GLOBAL_ATTRIBUTE15,
			C1.GLOBAL_ATTRIBUTE16,
			C1.GLOBAL_ATTRIBUTE17,
			C1.GLOBAL_ATTRIBUTE18,
			C1.GLOBAL_ATTRIBUTE19,
			C1.GLOBAL_ATTRIBUTE20,
			C1.TRANSFER_PRIORITY,
			CAPBRIMP_MAPPING.GetBankACMapping(C1.EXTERNAL_BANK_ACCOUNT_ID),		--EXTERNAL_BANK_ACCOUNT_ID
			C1.STAMP_DUTY_AMT,
			C1.STAMP_DUTY_BASE_AMT,
			C1.MRC_CLEARED_BASE_AMOUNT,
			C1.MRC_CLEARED_EXCHANGE_RATE,
			C1.MRC_CLEARED_EXCHANGE_DATE,
			C1.MRC_CLEARED_EXCHANGE_RATE_TYPE,
			C1.MRC_EXCHANGE_RATE,
			C1.MRC_EXCHANGE_DATE,
			C1.MRC_EXCHANGE_RATE_TYPE,
			C1.MRC_BASE_AMOUNT,
			C1.MRC_CLEARED_ERROR_BASE_AMOUNT,
			C1.MRC_CLEARED_CHARGES_BASE_AMT,
			C1.MRC_STAMP_DUTY_BASE_AMT,
			C1.MATURITY_EXCHANGE_DATE,
			C1.MATURITY_EXCHANGE_RATE_TYPE,
			C1.MATURITY_EXCHANGE_RATE,
			C1.DESCRIPTION,
			C1.ACTUAL_VALUE_DATE,
			C1.ANTICIPATED_VALUE_DATE,
			C1.RELEASED_DATE,
			C1.STOPPED_DATE,
			C1.MRC_MATURITY_EXG_DATE,
			C1.MRC_MATURITY_EXG_RATE,
			C1.MRC_MATURITY_EXG_RATE_TYPE,
			C1.IBAN_NUMBER
			);
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

ALTER TRIGGER AP_CHECKS_CBEI ENABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU1 ENABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU2 ENABLE;
ALTER TRIGGER AX_AP_CHECKS_BRI1 ENABLE;
